Why to Bother

Why you may need to extend MySQL

• you know why
• to add functionality that we have no time to add
• adding features that we prefer not to have in the server to avoid code bloat
• making the server to do what you need!

A Bit of a History

Traditional ways of extending MySQL

• Compiled-in or “Native” functions
  • allow to use statically compiled server
• Loadable User-Defined Functions (UDFs)
  • can be loaded and unloaded dynamically into the running server
  • mostly independent from the server's internals
• Procedures
  • a filter that it is put after the select, but before the data are sent

Known Problems

Drawbacks of the traditonal solutions

• Compiled-in or “Native” functions
  • require constant maintainance
  • difficult to install
• Loadable User-Defined Functions (UDFs)
  • incompatible API changes will result in a crash
• Procedures
  • only statically compiled, poorly documented

Plugins: easy of building

Easy to compile

• Support for compilation without MySQL sources
• No patching of MySQL sources for building in MySQL source tree
• Good ./configure support when building in MySQL source tree
• Support for both static and dynamic builds

Plugins: ease of distribution

Easy to distribute

• Source and binary distribution without MySQL
• API separated from the server internals
• Versioning to detect ABI changes safely

Plugins: ease of use

Easy to use

• Can be loaded and unloaded dynamically
• Maintainance SQL statements work for all plugins identically
• No “magic” arguments to install:

  CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'aggr.so';
  
  INSTALL PLUGIN median SONAME 'aggr.so'

Plugin API

• Generic API for all plugin types, sub-APIs for specific plugins
• Built-in automatic versioning both on API and sub-API level
• Installed in /usr/include/mysql/
  • API doesn't depend on internal MySQL headers
  • few sub-APIs still rely on server internals

API Features

Any plugin can

• provide the code for mysqld to execute
• declare new status variables (SHOW STATUS)
• declare new server variables (SHOW VARIABLES, @@var)
• introduce new command-line options (--plugin-option=value)

Plugin administration

• INSTALL PLUGIN foo SONAME 'bar.so'
• UNINSTALL PLUGIN foo
• SHOW PLUGINS
• INFORMATION_SCHEMA.PLUGINS
• --plugin-dir=/path/to/dir

Plugin administration

mysql> SELECT * FROM INFORMATION_SCHEMA.PLUGINS LIMIT 1 \G
********************* 1. row *************************
           PLUGIN_NAME: foo
        PLUGIN_VERSION: 2.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: FTPARSER
   PLUGIN_TYPE_VERSION: 0.1
        PLUGIN_LIBRARY: bar.so
PLUGIN_LIBRARY_VERSION: 0.0
         PLUGIN_AUTHOR: F.O.O.Bar
    PLUGIN_DESCRIPTION: Super-hyper-cool foobar parser

Plugin types

• Text parser for full-text search
• Storage engine
• Daemon (do-nothing plugin)
• Table in the INFORMATION_SCHEMA

  ---

• Audit logging
• Stored procedure language
• Query rewrite

Full-Text Parser Plugins

• The code that changes the text before it goes into FULLTEXT index.
• Can be used to search non-plaintext data formats, such as pdf, doc, mp3
• Can be used to parse CJK texts
• Can be used to apply your own word splitting rules, stemming, thesaurus...
• “Clean” and simple API

Full-Text Parser Examples

• from_file parser (by me ☺)
  • Simple example of a parser plugin. Assuming that you insert a filename in a column, it will index file's content.
• Porter stemming fulltext plugin (by Brian Loudon)
• mnoGoSearch stemming parser (by Alexander Barkov)
• Simple n-gram fulltext plugin (by Ado Nishimura)
  • Parser for Japanese and Chinese languages
• http://forge.mysql.com/projects/search.php?t=tag&k=fulltext

Storage Engine Plugins

• Traditional Storage Engine API, shaped to follow Plugin API conventions
• Every storage engine is a plugin now
• Most support only static linking, though
• API is neither “clean” nor simple
  • highly dependent on server internals
  • cannot be built outside of the MySQL source tree
  • versioning binds a plugin binary to specific server release

Storage Engine Examples

• MySQL storage engines — MyISAM, MEMORY, Maria, Falcon, NDB Cluster.
• Simple (one source file) MySQL engines — Archive, Blackhole, CSV
• Third party engines — InnoDB, PBXT, Nitro.
• Meta-engines — table functions engine (Brian Aker), wormhole engine (Jan Kneschke).

Daemon Plugins

• The most simple plugin type ever possible
• No functionality besides init/deinit callbacks
• “Clean”, obviously
• See heartbeat plugin example in the MySQL source tree

INFORMATION_SCHEMA Plugins

• Internal MySQL structures that describe INFORMATION_SCHEMA tables exported as a plugin
• Simple but not “clean” API
• Often used by storage engine vendors to export read-only performance/status data

Audit Plugins

• New, in 6.0 since March
• At certain points in the execution flow a server emits audit events
• Plugin can register to receive audit evens of a certain class or a set of classes
• Can be used, for example, for logging according to specific audit policy
• API is “clean”

Stored Procedure Language Plugins

• A project on Forge, developed by MySQL developers
• Standard syntax CREATE PROCEDURE ... LANGUAGE ...
• currently supports Java, Perl, XML-RPC

  mysql> CREATE PROCEDURE test.test1() LANGUAGE Perl
     --> MODIFIES SQL DATA EXTERNAL NAME 'MyDBIExample::test1';

• http://forge.mysql.com/wiki/ ProjectPage_External_Language_Stored_Procedures

Query Rewrite Plugins

• In development
• A hook is placed before the parser
• Plugin can modify query text in an arbitrary way
  • for example, SQL extensions can be supported, as long as they could be translated back to SQL
  • or a completely new query language could be implemented
• “Clean”

The future of the Plugin API

Extending the API

• Service API for plugins (WL#3859, WL#2940, WL#3961)
• Plugin run-time dependencies (WL#3295)
• Loadable plugins on Windows (WL#3653)
• Parameters for INSTALL PLUGIN (WL#3868)
• Syntax extensions by plugins (WL#4067, WL#2938)
• Continue the work on simplifying and “cleaning” sub-APIs

Applications of the Plugin API

New plugin types

• External language stored procedures (WL#820)
• Pluggable authentication support (WL#1054, WL#3118)
• Convert UDFs to plugin API (WL#2974)
• Replication (WL#3662)

Useful links

• Presentations
  • Brian Aker. Extending MySQL. Wednesday, 5:15pm, Ballroom F
  • Antony Curtis and Eric Herman. A Tour of External Language Stored Procedures for MySQL. Thursday, 11:55am, Ballroom A
• DevZone articles (http://dev.mysql.com/articles/)
  • Roland Bouman. MySQL Information Schema Plugins: the best kept secret of MySQL 5.1
  • Roland Bouman. Reporting MySQL Internals with Information Schema plug-ins